package cn.com.dao.impl;

import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import cn.com.dao.WebDao;
import cn.com.pojo.CountPerson;
import cn.com.pojo.Person;
import cn.com.pojo.PersonMappingQuery;
import cn.com.pojo.UpdatePerson;

public class WebDaoImpl extends JdbcDaoSupport implements WebDao {

	public void initDataBase() {
		String sql = "create table if not exists user (id int primary key auto_increment,name varchar(100),desc1 varchar(200))";
		this.getJdbcTemplate().execute(sql);
	}

	@Override
	public List<Person> selectAllInfo() {
		String sql = "select * from user";
		List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql);
		List<Person> personList = new ArrayList<Person>();
		for (Map<String, Object> param : list) {
			Person person = new Person();
			person.setId((Integer) param.get("id"));
			person.setName(String.valueOf(param.get("name")));
			person.setDesc(String.valueOf(param.get("desc")));
			personList.add(person);
		}
		return personList;
	}

	@Override
	public void addPerson(Person person) {
		String sql = "insert into user(name,desc1) values (?,?)";
		this.getJdbcTemplate().update(sql, new Object[] { person.getName(), person.getDesc() });
	}

	@Override
	public String getPersonName(Integer id) {
		String sql = "select name from user where id= " + id;
		return (String)this.getJdbcTemplate().queryForObject(sql, String.class);
	}

	@Override
	public int getPersonsCount() {
		String sql ="select count(*) from user";
		return this.getJdbcTemplate().queryForInt(sql);
	}

	/**
	 * 使用MappingSqlQuery来执行查询
	 */
	@Override
	public List<Person> selectAllInfo1(Person person) {
		PersonMappingQuery pmq = new PersonMappingQuery();
		pmq.setDataSource(getDataSource());
		pmq.setSql(" select * from user where name=? ");
		pmq.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));
		pmq.compile();
		return pmq.execute(new Object[]{person.getName()});
	}

	/**
	 * 使用SqlUpdate执行更新
	 */
	@Override
	public int updatePerson(Person person) {
		UpdatePerson update = new UpdatePerson(getDataSource());
		return  update.run(person);
	}

	/**
	 * 使用SqlFunction返回单一结果
	 */
	@Override
	public int count1(Person person) {
		CountPerson tool = new CountPerson(getDataSource(), "select count(*) from user where name = ?");
		tool.sqlFunction.declareParameter(new SqlParameter(Types.VARCHAR));
		tool.sqlFunction.compile();
		return tool.sqlFunction.run(new Object[]{person.getName()});
	}

}
